package com.ldl.dao.impl;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ldl.dao.LdlDao;
import com.ldl.entity.Ldl;
import com.ldl.util.DBHelper;
import com.sun.corba.se.spi.orbutil.fsm.Guard.Result;



/**
 * @author Administrator
 * Author : L.D.L
 * E-mail : 1419485293@qq.com
 * Date : 2020年7月16日下午7:05:48
 */
public class LdlDaoImpl implements LdlDao{

	/* (non-Javadoc)
	 * @see com.ldl.dao.dao#add(com.ldl.pojo.ldl)
	 */
	@Override
	public int add(Ldl u) throws SQLException{
		String sql = "insert into ldl(ldl_name,ldl_birth,ldl_sex,ldl_age) value(?,?,?,?)";
		try (
				Connection conn = DBHelper.getConn();
				PreparedStatement ps = conn.prepareStatement(sql);
			){
			ps.setObject(1, u.getLdlName());
			ps.setObject(2, u.getLdlBirth());
			ps.setObject(3, u.getLdlSex());
			ps.setObject(4, u.getLdlAge());
			
			return ps.executeUpdate();
		} 
	}

	/* (non-Javadoc)
	 * @see com.ldl.dao.dao#updateById(int)
	 */
	@Override
	public int updateById(Ldl u) throws SQLException{
		String sql = "update ldl set ldl_name = ?, ldl_birth =?, ldl_sex = ?, ldl_age=? where ldl_id = ?";
		try(
				Connection conn = DBHelper.getConn();
				PreparedStatement ps = conn.prepareStatement(sql);
			) {
			ps.setObject(1, u.getLdlName());
			ps.setObject(2, u.getLdlBirth());
			ps.setObject(3, u.getLdlSex());
			ps.setObject(4, u.getLdlAge());
			ps.setObject(5, u.getLdlId());
			
			return ps.executeUpdate();
		} 
	}

	/* (non-Javadoc)
	 * @see com.ldl.dao.dao#deleteById(int)
	 */
	@Override
	public int deleteById(Serializable id) throws SQLException{
		String sql = "delete from ldl where ldl_id = ?";
		try(
				Connection conn = DBHelper.getConn();
				PreparedStatement ps = conn.prepareStatement(sql);
			) {
			ps.setObject(1, id);
			return ps.executeUpdate();
		} 
	}

	/* (non-Javadoc)
	 * @see com.ldl.dao.UserDao#selectById(int)
	 */
	@Override
	public Ldl selectById(Serializable id) throws SQLException{
		String sql = "select * from ldl where ldl_id =? ";
		Ldl u = new Ldl();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			conn = DBHelper.getConn();
			ps = conn.prepareStatement(sql);
			ps.setObject(1, id);
			res = ps.executeQuery();
			while(res.next()){
				u.setLdlId(res.getInt("ldl_id"));
				u.setLdlName(res.getString("ldl_name"));
				u.setLdlBirth(res.getDate("ldl_birth"));
				u.setLdlSex(res.getString("ldl_sex"));
				u.setLdlAge(res.getInt("ldl_age"));
			}
		}finally{
			DBHelper.close(conn, ps, res);
		}
		return u;
	}
	/* (non-Javadoc)
	 * @see com.ldl.dao.dao#selectAll(int)
	 */
	@Override
	public List<Ldl> selectAll(int start,int len) throws SQLException{
		String sql = "select * from ldl limit "+start+","+len;
		List<Ldl> list = new ArrayList<Ldl>();
		try(
				Connection conn = DBHelper.getConn();
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet res = ps.executeQuery();
			) {
			while(res.next()){
				Ldl u = new Ldl();
				u.setLdlId(res.getInt("ldl_id"));
				u.setLdlName(res.getString("ldl_name"));
				u.setLdlBirth(res.getDate("ldl_birth"));
				u.setLdlSex(res.getString("ldl_sex"));
				u.setLdlAge(res.getInt("ldl_age"));
				list.add(u);
			}
			return list;
		} 
	}

	/* (non-Javadoc)
	 * @see com.ldl.dao.dao#countAll()
	 */
	@Override
	public int countAll(String name) throws SQLException{
		String sql = "select count(*) from ldl where ldl_name like '%"+name+"%'";
		int num = 0;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		try {
			conn = DBHelper.getConn();
			ps = conn.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()){
				num = res.getInt(1);
		    } 
		} finally{
			DBHelper.close(conn, ps, res);
		}
		return num;
		
	}

	@Override
	public List<Ldl> search(String name,int start , int end)
			throws SQLException {
		String sql = "select * from ldl where ldl_name like '%"+name+"%' limit "+start+","+end;
		List<Ldl> list = new ArrayList<Ldl>();
		try(
				Connection conn = DBHelper.getConn();
				PreparedStatement ps = conn.prepareStatement(sql);
				ResultSet res = ps.executeQuery();
			) {
			while(res.next()){
				Ldl u = new Ldl();
				u.setLdlId(res.getInt("ldl_id"));
				u.setLdlName(res.getString("ldl_name"));
				u.setLdlBirth(res.getDate("ldl_birth"));
				u.setLdlSex(res.getString("ldl_sex"));
				u.setLdlAge(res.getInt("ldl_age"));
				list.add(u);
			}
			return list;
		} 
	}

}
